/* eslint-disable class-methods-use-this */
/* eslint-disable prefer-template */
import { SqlHelper } from '../../lib/utils/sqlHelper'
import { DbColumn } from '../../models/dbColumn'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbTable } from '../../models/dbTable'
import { DbProvider, line } from '../core/dbProvider'

export class PostgreSqlProvider extends DbProvider {
	/* 
		// PostgreSQL
		'Syntax:
		'  Comment: -- or 
		'  string literal modifier: '
		'  Identifier modifier: "
		' Has concept of inherits
		' CREATE LANGUAGE plpgsql;
		'
		'CREATE INDEX ik_1
		'  ON sampletable2
		'  USING btree
		'  (subitemid);
		'ALTER TABLE sampletable2 CLUSTER ON ik_1;
		'
		// Index: ik_2
		'
		// DROP INDEX ik_2;
		'
		'CREATE UNIQUE INDEX ik_2
		'  ON sampletable2
		'  USING btree
		'  (subitemid);
	*/

	private SQL_Statement_End = ';'

	private OutputID_FirstSection = 1

	private OutputID_SecondSection = 2

	public getCreateTableIfNotExistsSql(model: DbModel): string {
		this.outputInitialize()
		this.GetSQLCreateTableInternal(model, false, true)
		return this.outputCopy()
	}

	public getCreateTableSql(model: DbModel, withComment: boolean): string {
		this.outputInitialize()
		this.GetSQLCreateTableInternal(model, withComment, false)
		return this.outputCopy()
	}

	public getDropTableSql(model: DbModel) {
		this.outputInitialize()
		// Drop table SQL
		this.GetSQLDropTableInternal(model)
		return this.outputCopy()
	}

	public getDropThenCreateTableSql(model: DbModel, withComment: boolean): string {
		this.outputInitialize()

		// Drop table SQL
		this.GetSQLDropTableInternal(model)

		// Create tables SQL
		this.GetSQLCreateTableInternal(model, withComment, false)

		return this.outputCopy()
	}

	private GetSQLCreateTableInternal(model: DbModel, withComment: boolean, withIfNotExists: boolean) {
		// Create tables
		model.tables.forEach((table: DbTable) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSqlCreateTableSingle(table, withComment, withIfNotExists)
			this.outputlnln(sqlCreateTable)
			if (sqlCreateFK) {
				this.outputlnln(sqlCreateFK, this.OutputID_FirstSection)
			}
		})
	}

	public getSqlCreateTableSingle(
		table: DbTable,
		withComment: boolean,
		withIfNotExists: boolean,
	): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = {
			sqlCreateFK: '',
			sqlCreateTable: '',
		}

		if (!table.columns) {
			return result
		}
		// Create Columns sql
		let sqlColumn = ''
		table.columns.forEach((column: DbColumn, index) => {
			const sqlComma = index === 0 ? ' ' : ','
			const sqlNullable = column.nullable ? 'NULL' : 'NOT NULL'
			const sqlDefault = column.default!.length === 0 ? '' : ` DEFAULT ${this.stripBrackets(column.default!)}`
			sqlColumn += line + `  ${sqlComma}${column.name} ${column.type} ${sqlNullable}${sqlDefault}`
		})

		// Primary key SQL
		let sqlPrimaryKey = ''
		if (table.primaryKey) {
			const sqlPkName = this.renderPkName(table)
			const sqlPkColumns = table.primaryKey.columns.replace(',\\s*', ', ')
			sqlPrimaryKey = line + `  ,CONSTRAINT ${sqlPkName} PRIMARY KEY (${sqlPkColumns})`
		}

		// unique Indexes SQL
		let sqlUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				sqlUniqueIndex += line + `  ,CONSTRAINT ${sqlIndexName} UNIQUE (${sqlColumns})`
			}
		})

		// non-unique Indexes SQL
		let sqlNoUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (!index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				sqlNoUniqueIndex +=
					line + `CREATE INDEX ${sqlIndexName} ON ${table.name} (${sqlColumns})` + this.SQL_Statement_End
			}
		})

		// Generate table sql
		// Generate Foreign key SQL
		if (table.foreignKeys.length > 0) {
			result.sqlCreateFK = `-- Create foreign keys for table: '${table.name}'`
			table.foreignKeys.forEach((foreignKey: DbForeignKey) => {
				const sqlFkName = this.renderFkName(table, foreignKey)
				const sqlColumns = foreignKey.columns.replace(',\\s*', ', ')
				const sqlRefInfo = `${foreignKey.refTable}(${foreignKey.refColumns.replace(',\\s*', ', ')})`
				const sqlOptions = foreignKey.options!.length > 0 ? foreignKey.options : ''
				result.sqlCreateFK +=
					line +
					`ALTER TABLE ${table.name}` +
					` ADD CONSTRAINT ${sqlFkName} FOREIGN KEY (${sqlColumns}) REFERENCES ${sqlRefInfo}${sqlOptions}` +
					this.SQL_Statement_End
			})
		}

		// table comment
		let sqlComment = ''

		// Column comment
		if (withComment) {
			if (withComment && table.comment) {
				sqlComment = line + `COMMENT ON TABLE ${table.name} IS ${SqlHelper.toSQL(table.comment)};`
			}
			table.columns.forEach((column: DbColumn) => {
				let sqlColumnComment = this.getColumnComment(column)
				if (sqlColumnComment.length > 0) {
					sqlColumnComment = SqlHelper.toSQL(sqlColumnComment)
					sqlComment += line + `COMMENT ON COLUMN ${table.name}.${column.name} IS ${sqlColumnComment};`
				}
			})
		}
		const sqlIfNotExist = withIfNotExists ? 'IF NOT EXISTS ' : ''
		result.sqlCreateTable =
			`-- Create table: '${table.name}'` +
			line +
			`CREATE TABLE ${sqlIfNotExist}${table.name} (` +
			sqlColumn +
			sqlPrimaryKey +
			sqlUniqueIndex +
			line +
			')' +
			this.SQL_Statement_End +
			sqlNoUniqueIndex +
			sqlComment

		return result
	}

	public GetSQLDropTableInternal(model: DbModel) {
		// Drop tables
		model.tables.forEach((table: DbTable) => {
			const sSQL = this.getSqlDropTableSingle(table)
			this.outputlnln(sSQL)
		})
	}

	private getSqlDropTableSingle(table: DbTable): string {
		const sSQL = 'DROP TABLE IF EXISTS ' + table.name + ' CASCADE' + this.SQL_Statement_End

		return sSQL
	}
}
